﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.DLR;
using Workflows.Components.Reports.OpenXmlUtil;

namespace Workflows.Components.Reports
{
	/// <summary>
	/// 
	/// </summary>
	public class SetManipulationWorker:IWorker
	{
		#region transform compiled instances
		private Hashtable htTransformers;
		#endregion

		#region Implementation of IWorker

		public event WorkerStarted WorkerStartedEvent;
		public event WorkerFinished WorkerFinishedEvent;
		public event WorkerHasError WorkerErredEvent;
		public event WorkerCancelled WorkerCancelledEvent;
		public event WorkerStatusChanged WorkerStatusChangedEvent;

		private Thread workerThread;
		private SetManipulationInstruction instruction;
		private SetManipulationOutput output;
		private RunContext _Context;
		private Guid _TicketID;
		private string _WorkflowName;
		private string _ComponentName;

		/// <summary>
		/// 
		/// </summary>
		public string ComponentName
		{
			get { return _ComponentName; }
		}

		/// <summary>
		/// 
		/// </summary>
		public string WorkflowName
		{
			get { return _WorkflowName; }
		}

		/// <summary></summary>
		public Thread WorkerThread
		{
			get { return workerThread; }
		}
		/// <summary></summary>
		public InstructionBase Instruction
		{
			get { return instruction; }
		}
		/// <summary></summary>
		public ResultBase Output
		{
			get { return output; }
		}
		/// <summary></summary>
		public Type ComponentType
		{
			get { return typeof(SetManipulationComponent); }
		}
		/// <summary></summary>
		public RunContext Context
		{
			get { return _Context; }
			set { _Context = value; }
		}
		/// <summary></summary>
		public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
		{
			this._TicketID = ticketGuid;
			this.instruction = (SetManipulationInstruction)request;
			this._WorkflowName = workflowName;
			this._ComponentName = componentName;
		}
		/// <summary></summary>
		public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
		{
			this._TicketID = ticketGuid;
			this.instruction = (SetManipulationInstruction)request;
			this._Context = context;
			this._WorkflowName = workflowName;
			this._ComponentName = componentName;
		}
		/// <summary></summary>
		public void DoWork()
		{
			if (this.WorkerStartedEvent != null)
			{
				this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
			}
			this.workerThread = Thread.CurrentThread;
			this.ManipulateSets();
		}
		/// <summary></summary>
		public void DoWorkAsync()
		{
			if (this.WorkerStartedEvent != null)
			{
				this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
			}
			this.workerThread = new Thread(new ThreadStart(this.ManipulateSets));
			this.workerThread.Start();
		}
		/// <summary></summary>
		public void CancelWork()
		{
			if (this.workerThread != null && this.workerThread.IsAlive)
			{
				this.workerThread.Abort();
				this.workerThread.Join();

				if (this.WorkerCancelledEvent != null)
				{
					this.WorkerCancelledEvent(this._TicketID, this.GetType(), this.instruction);
				}
			}
		}

		#endregion

		#region work
		private void ManipulateSets()
		{
			try
			{
				DataSet ds=new DataSet();
				foreach(string setName in this.instruction.Providers.Keys)
				{
                    if(this.WorkerStatusChangedEvent !=null)
                    {
                        string msg = string.Format("Getting data from provider {0}...", setName);
                        int pct = 5;
                        this.WorkerStatusChangedEvent(
                            this._TicketID, this.GetType(), this.instruction,
                            this._WorkflowName, this._ComponentName, msg, pct);
                    }
					ds.Tables.Add(this.GetDataFromProvider(this.instruction.Providers[setName]));
				}

                this.instruction.Operations.Sort();
				foreach(SetOperation op in this.instruction.Operations)
				{
                    if (this.WorkerStatusChangedEvent != null)
                    {
                        string msg =
                            string.Format("Performing {0} to generating set {1}...",
                                          op.OpType.ToString(),
                                          op.OutputSetName);
                        int pct = 15;
                        this.WorkerStatusChangedEvent(
                            this._TicketID, this.GetType(), this.instruction,
                            this._WorkflowName, this._ComponentName, msg, pct);
                    }
					ds.Tables.Add(this.PerformSetOperation(ds, op));
				}

				DataSet resultDataSet =new DataSet("Output");
				foreach(DataTable dt in ds.Tables)
				{
					if(this.instruction.ExportedDataSetNames !=null && this.instruction.ExportedDataSetNames.Contains(dt.TableName))
					{
						resultDataSet.Tables.Add(dt.Copy());
					}
				}

                if (this.WorkerStatusChangedEvent != null)
                {
                    string msg = "Writing output...";
                    int pct = 95;
                    this.WorkerStatusChangedEvent(
                        this._TicketID, this.GetType(), this.instruction,
                        this._WorkflowName, this._ComponentName, msg, pct);
                }
				if (this.instruction.ResultFilePath.ToLower().EndsWith(".xml"))
					resultDataSet.WriteXml(this.instruction.ResultFilePath);
				else
					ExcelFileHelper.WriteWorksheetData(this.instruction.ResultFilePath, resultDataSet);

				this.output=new SetManipulationOutput(this.instruction.OwnerComponentID,this._TicketID,this.instruction.ResultFilePath);
				if (this.WorkerFinishedEvent != null)
					this.WorkerFinishedEvent(this._TicketID, this.GetType(), this.instruction, this.output);
			}
			catch(Exception ex)
			{
				if (this.WorkerErredEvent != null)
					this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex.ToString(), true);
			}
		}
		#endregion

		#region providers 
		private DataTable GetDataFromProvider(SetDataProvider provider)
		{
		    return provider.GetDataFromProvider();
		}
		#endregion

		#region set operations
		private DataTable PerformSetOperation(DataSet ds, SetOperation operation)
		{
			string setName1 = string.Empty;
			string setName2 = string.Empty;
			string filterClause = string.Empty;
			SetManipulationMethod setMethod = SetManipulationMethod.Union;
			operation.ParseFormular(out setMethod, out setName1, out setName2, out filterClause);
			SetJoin setJoin = null;
			foreach(SetJoin join in this.instruction.Joins)
			{
				if(join.LeftSetName==setName1 && join.RightSetName==setName2)
				{
					setJoin = join;
					break;
				}
			}
			switch(setMethod)
			{
				case SetManipulationMethod.Union:
					return PerformUnion(ds.Tables[setName1], ds.Tables[setName2], setJoin, operation.OutputSetName);
				case SetManipulationMethod.Intersection:
					return PerformIntersection(ds.Tables[setName1], ds.Tables[setName2], setJoin, operation.OutputSetName);
				case SetManipulationMethod.Exclude:
					return PerformExclusion(ds.Tables[setName1], ds.Tables[setName2], setJoin, operation.OutputSetName);
				case SetManipulationMethod.Filter:
					return PerformFilter(ds.Tables[setName1], filterClause, operation.OutputSetName);
				default:
					return null;
			}
		}

		private DataTable PerformFilter(DataTable dt, string filter, string outputSetName)
		{
            Regex partRegex=new Regex(@"and\s+|or\s+",RegexOptions.IgnoreCase);
		    string[] filterOps = partRegex.Split(filter);
            Regex opRegex=new Regex(@"<|>|in|=",RegexOptions.IgnoreCase);
		    List<string> numColumns = new List<string>();
            List<string> dateColumns = new List<string>();
		    List<string> bitColumns = new List<string>();
            foreach(string filterOp in filterOps)
            {
                string[] expressionPair = opRegex.Split(filterOp);
                if(expressionPair.Length==2)
                {
                    string evalPart = expressionPair[1];
                    string colName = expressionPair[0].ToLower();
                    if (evalPart.IndexOf("'")>=0)
                    {
                        // date?
                        DateTime dateValue = DateTime.Now;
                        string datePart = evalPart.Replace("'", "");
                        bool canParseDate = DateTime.TryParse(datePart, out dateValue);
                        if(canParseDate)
                        {
                            if(!dateColumns.Contains(colName))
                                dateColumns.Add(colName);
                        }
                    }
                    else
                    {
                        if(evalPart=="0" || evalPart=="1")
                        {
                            if(!bitColumns.Contains(colName))
                                bitColumns.Add(colName);
                        }
                        else
                        {
                            if(!numColumns.Contains(colName))
                                numColumns.Add(colName);
                        }
                    }
                }
            }

            DataTable dtCopy =new DataTable();
            foreach(DataColumn col in dt.Columns)
            {
                if(numColumns.Contains(col.ColumnName.ToLower()))
                {
                    dtCopy.Columns.Add(col.ColumnName, typeof(double));
                }
                else if(bitColumns.Contains(col.ColumnName.ToLower()))
                {
                    dtCopy.Columns.Add(col.ColumnName, typeof(bool));
                }
                else if(dateColumns.Contains(col.ColumnName.ToLower()))
                {
                    dtCopy.Columns.Add(col.ColumnName, typeof(DateTime));
                }
                else
                {
                    dtCopy.Columns.Add(col.ColumnName, typeof(string));
                }
            }
            foreach(DataRow dr in dt.Rows)
            {
                DataRow drCopy = dtCopy.NewRow();
                for(int i=0;i<dt.Columns.Count;i++)
                {
                    if(dr[i]==null || dr[i]==DBNull.Value || dr[i].ToString().Length==0)
                        continue;
                    
                    if(numColumns.Contains(dt.Columns[i].ColumnName.ToLower()))
                    {
                        drCopy[i] = double.Parse(dr[i].ToString());
                    }
                    else if(bitColumns.Contains(dt.Columns[i].ColumnName.ToLower()))
                    {
                        drCopy[i] = bool.Parse(dr[i].ToString());
                    }
                    else if(dateColumns.Contains(dt.Columns[i].ColumnName.ToLower()))
                    {
                        drCopy[i] = DateTime.Parse(dr[i].ToString());
                    }
                    else
                    {
                        drCopy[i] = dr[i];
                    }
                }
                dtCopy.Rows.Add(drCopy);
            }

            DataRow[] drs = dtCopy.Select(filter);
		    DataTable outputDt = dtCopy.Clone();
			if(drs !=null && drs.Length>0)
			{
				foreach(DataRow dr in drs)
				{
					DataRow newDr = outputDt.NewRow();
					for(int i=0;i<dt.Columns.Count;i++)
					{
						newDr[i] = dr[i];
					}
					outputDt.Rows.Add(newDr);
				}
			}
			outputDt.TableName = outputSetName;
			return outputDt;
		}

		private DataTable PerformExclusion(DataTable dtSrc, DataTable dtExclude, SetJoin join, string outputSetName)
		{
			DataTable dtOutput = dtSrc.Clone();

			List<string> keysToRemove=new List<string>();
			foreach(DataRow dr in dtExclude.Rows)
			{
				string keyToRemove = this.EvaluateKey(dr, join.RightKeyNames, join.RightKeyTransformation);
				keysToRemove.Add(keyToRemove.ToLower());
			}
			for(int i=0;i<dtSrc.Rows.Count;i++)
			{
				string srcKey = this.EvaluateKey(dtSrc.Rows[i], join.LeftKeyNames, join.LeftKeyTransformation);
				if(!keysToRemove.Contains(srcKey.ToLower()))
				{
					DataRow dr = dtOutput.NewRow();
					for(int k=0;k<dtSrc.Columns.Count;k++)
					{
						dr[k] = dtSrc.Rows[i][k];
					}
					dtOutput.Rows.Add(dr);
				}
			}

			dtOutput.TableName = outputSetName;
			return dtOutput;
		}

		private DataTable PerformUnion(DataTable dt1, DataTable dt2, SetJoin join, string outputSetName)
		{
			DataTable dt3 = new DataTable(outputSetName);
			dt3.Columns.Add(string.Format("{0}-Key", dt1.TableName), typeof (string));
			dt3.Columns.Add(string.Format("{0}-Key", dt2.TableName), typeof(string));
			foreach(DataColumn col in dt1.Columns)
			{
				if (!dt3.Columns.Contains(col.ColumnName))
					dt3.Columns.Add(col.ColumnName, col.DataType);
			}
			foreach(DataColumn col in dt2.Columns)
			{
				if (!dt3.Columns.Contains(col.ColumnName))
					dt3.Columns.Add(col.ColumnName, col.DataType);
			}

			Dictionary<string,int> key1Rows=new Dictionary<string, int>();
			for(int i=0;i<dt1.Rows.Count;i++)
			{
				DataRow dr1 = dt1.Rows[i];
				string key1 = this.EvaluateKey(dr1, join.LeftKeyNames, join.LeftKeyTransformation);
				if (!key1Rows.ContainsKey(key1))
					key1Rows.Add(key1.ToLower(), i);
			}
			Dictionary<string, int> key2Rows = new Dictionary<string, int>();
			for (int i = 0; i < dt2.Rows.Count; i++)
			{
				DataRow dr2 = dt2.Rows[i];
				string key2 = this.EvaluateKey(dr2, join.RightKeyNames, join.RightKeyTransformation);
				if (!key2Rows.ContainsKey(key2))
					key2Rows.Add(key2.ToLower(), i);
			}
			foreach(string key1 in key1Rows.Keys)
			{
				DataRow dr1 = dt1.Rows[key1Rows[key1]];
				DataRow dr3 = dt3.NewRow();
				dr3[string.Format("{0}-Key", dt1.TableName)] = key1;
				dr3[string.Format("{0}-Key", dt2.TableName)] = key1;
				if (key2Rows.ContainsKey(key1.ToLower()))
				{
					foreach(DataColumn col1 in dt1.Columns)
					{
						if (dt3.Columns.Contains(col1.ColumnName))
							dr3[col1.ColumnName] = dr1[col1.ColumnName];
					}
					DataRow dr2 = dt2.Rows[key2Rows[key1.ToLower()]];
					foreach(DataColumn col2 in dt2.Columns)
					{
						if (dt3.Columns.Contains(col2.ColumnName))
							dr3[col2.ColumnName] = dr2[col2.ColumnName];
					}
				}
				else
				{
					foreach (DataColumn col1 in dt1.Columns)
					{
						if (dt3.Columns.Contains(col1.ColumnName))
							dr3[col1.ColumnName] = dr1[col1.ColumnName];
					}
				}
				dt3.Rows.Add(dr3);
			}
			foreach(string key2 in key2Rows.Keys)
			{
				DataRow dr2 = dt2.Rows[key2Rows[key2]];
				if(!key1Rows.ContainsKey(key2))
				{
					DataRow dr3 = dt3.NewRow();
					dr3[string.Format("{0}-Key", dt1.TableName)] = key2;
					dr3[string.Format("{0}-Key", dt2.TableName)] = key2;
					foreach (DataColumn col2 in dt2.Columns)
					{
						if (dt3.Columns.Contains(col2.ColumnName))
							dr3[col2.ColumnName] = dr2[col2.ColumnName];
					}
					dt3.Rows.Add(dr3);
				}
			}
			return dt3;
		}

		private DataTable PerformIntersection(DataTable dt1, DataTable dt2, SetJoin join, string outputSetName)
		{
			DataTable dt3 = new DataTable(outputSetName);
			dt3.Columns.Add(string.Format("{0}-Key", dt1.TableName), typeof(string));
			dt3.Columns.Add(string.Format("{0}-Key", dt2.TableName), typeof(string));
			foreach (DataColumn col in dt1.Columns)
			{
				if (!dt3.Columns.Contains(col.ColumnName))
					dt3.Columns.Add(col.ColumnName, col.DataType);
			}
			foreach (DataColumn col in dt2.Columns)
			{
				if (!dt3.Columns.Contains(col.ColumnName))
					dt3.Columns.Add(col.ColumnName, col.DataType);
			}

			Dictionary<string, int> key2Rows = new Dictionary<string, int>();
			for (int i = 0; i < dt2.Rows.Count; i++)
			{
				DataRow dr2 = dt2.Rows[i];
				string key2 = this.EvaluateKey(dr2, join.RightKeyNames, join.RightKeyTransformation);
				if (!key2Rows.ContainsKey(key2))
					key2Rows.Add(key2.ToLower(), i);
			}
			foreach (DataRow dr1 in dt1.Rows)
			{
				string key1 = this.EvaluateKey(dr1, join.LeftKeyNames, join.LeftKeyTransformation);
				if (key2Rows.ContainsKey(key1.ToLower()))
				{
					DataRow dr3 = dt3.NewRow();
					dr3[string.Format("{0}-Key", dt1.TableName)] = key1;
					dr3[string.Format("{0}-Key", dt2.TableName)] = key1;
					foreach (DataColumn col1 in dt1.Columns)
					{
						if (dt3.Columns.Contains(col1.ColumnName))
							dr3[col1.ColumnName] = dr1[col1.ColumnName];
					}
					DataRow dr2 = dt2.Rows[key2Rows[key1.ToLower()]];
					foreach (DataColumn col2 in dt2.Columns)
					{
						if (dt3.Columns.Contains(col2.ColumnName))
							dr3[col2.ColumnName] = dr2[col2.ColumnName];
					}
					dt3.Rows.Add(dr3);
				}
			}
			return dt3;
		}
		#endregion

		#region join
		private string EvaluateKey(DataRow dr, string[] keyNames, string transformation)
		{
			if (keyNames == null || keyNames.Length == 0)
				throw new Exception("Unable to evaluate join keys");
			int hashKey = keyNames.GetHashCode();
			if (!string.IsNullOrEmpty(transformation))
			{
				if (this.htTransformers == null)
					this.htTransformers = new Hashtable();
				object compiledTransformer = null;
				List<MethodArgument> args = new List<MethodArgument>();
				foreach (string keyName in keyNames)
				{
					string argValue = null;
					if (dr[keyName] != null && dr[keyName] != DBNull.Value)
						argValue = dr[keyName].ToString();
					MethodArgument methodArg = new MethodArgument(keyName, typeof(string), false, argValue);
					args.Add(methodArg);
				}
				MethodConstructInfo methodBuilder = new MethodConstructInfo(typeof (string), "Transform", args, transformation);

				if (this.htTransformers.ContainsKey(hashKey))
				{
					compiledTransformer = this.htTransformers[hashKey];
				}
				else
				{
					compiledTransformer = CodeDomUtil.CompileMethod(methodBuilder);
					this.htTransformers.Add(hashKey, compiledTransformer);
				}
				
				object returnObj = null;
				if (methodBuilder.Args != null && methodBuilder.Args.Count > 0)
				{
					List<object> argValues = new List<object>();
					foreach (MethodArgument arg in methodBuilder.Args)
					{
						argValues.Add(arg.ArgValue);
					}
					returnObj =
						compiledTransformer.GetType().InvokeMember(methodBuilder.MethodName, BindingFlags.InvokeMethod, null, compiledTransformer,
														argValues.ToArray());
				}
				else
				{
					returnObj =
						compiledTransformer.GetType().InvokeMember(methodBuilder.MethodName, BindingFlags.InvokeMethod, null, compiledTransformer, null);
				}
				string transformedKey = returnObj == null ? string.Empty : returnObj.ToString();
				return transformedKey;
			}
			else
			{
				if (keyNames.Length == 1)
					return dr[keyNames[0]].ToString();
				else
				{
					StringBuilder buffer = new StringBuilder();
					foreach (string leftKey in keyNames)
					{
						buffer.Append(dr[leftKey].ToString());
					}
					return buffer.ToString();
				}
			}
		}
		#endregion
	}
}
